from datetime import datetime

import pymysql
from flask import Flask, jsonify, request
# pip install flask-cors
from flask_cors import cross_origin

from utils import create_connection

app = Flask(__name__)


@app.route('/api')
def api_index():
    return {'message': 'hello, world!'}


@app.route('/api/emp_counts')
@cross_origin()
def show_emp_counts():
    conn = create_connection(database='hrs')
    x_data, y_data = [], []
    try:
        with conn.cursor() as cursor:
            cursor.execute('select dname, total from vi_dept_emp_count')
            for dname, total in cursor.fetchall():
                x_data.append(dname)
                y_data.append(total)
    finally:
        conn.close()
    return jsonify({'names': x_data, 'totals': y_data})


@app.route('/api/depts')
@cross_origin()
def list_departments():
    """获取所有部门的数据"""
    data_dict = {'code': 10000, 'message': '获取数据成功'}
    conn = create_connection(database='hrs')
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute('select dno as no, dname as name, dloc as location from tb_dept')
            data_dict['departments'] = cursor.fetchall()
    except pymysql.MySQLError as err:
        print(err)
        data_dict['code'] = 10001
        data_dict['message'] = '获取数据失败'
    finally:
        conn.close()
    return jsonify(data_dict)


@app.route('/api/emps')
@cross_origin()
def list_employees():
    """获取所有员工或指定部门的员工数据"""
    data_dict = {'code': 10000, 'message': '获取数据成功'}
    conn = create_connection(database='hrs')
    try:
        page = int(request.args.get('page', '1'))
        size = int(request.args.get('size', '5'))
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            dno = request.args.get('dno', None)
            if dno:
                sql = 'select eno as no, ename as name, job, sal, dno ' \
                      'from tb_emp where dno=%s ' \
                      'order by eno limit %s offset %s'
                params = (dno, size, (page - 1) * size)
            else:
                sql = 'select eno as no, ename as name, job, sal from tb_emp ' \
                      'order by eno limit %s offset %s'
                params = (size, (page - 1) * size)
            cursor.execute(sql, params)
            data_dict['employees'] = cursor.fetchall()
    except (pymysql.MySQLError, ValueError) as err:
        print(err)
        data_dict['code'] = 10001
        data_dict['message'] = '获取数据失败'
    finally:
        conn.close()
    return data_dict


@app.route('/api/stocks')
@cross_origin()
def get_stock_data():
    data_dict = {'code': 10000, 'message': '获取数据成功'}
    conn = create_connection(database='viz')
    try:
        start = request.args.get('start', None)
        end = request.args.get('end', None)
        if start and end:
            start_time = datetime.strptime(start, '%Y-%m-%d')
            end_time = datetime.strptime(end, '%Y-%m-%d')
            dt = end_time - start_time
            if dt.days <= 31:
                with conn.cursor() as cursor:
                    cursor.execute(
                        'select date(`TxDate`) from tb_stock '
                        'where `TxDate` between %s and %s order by `TxDate`',
                        (start, end)
                    )
                    data_dict['x_axis'] = [str(row[0]) for row in cursor.fetchall()]
                    cursor.execute(
                        'select round(`Open`, 2), round(`Close`, 2), round(`Low`, 2), round(`High`, 2) from tb_stock '
                        'where `TxDate` between %s and %s order by `TxDate`',
                        (start, end)
                    )
                    data_dict['y_axis'] = cursor.fetchall()
            else:
                data_dict['code'] = 10003
                data_dict['message'] = '只支持31天范围内的查询'
        else:
            data_dict['code'] = 10002
            data_dict['message'] = '请指定查询时间范围'
    except pymysql.MySQLError as err:
        print(err)
        data_dict['code'] = 10001
        data_dict['message'] = '获取数据失败'
    finally:
        conn.close()
    return data_dict


if __name__ == '__main__':
    app.run(debug=True)
